Imports System.Data.SqlClient
Imports System.Data
Imports CAPA_Entidad
Imports CAPA_Utilitarios
Public Class UbicacionDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function actualizarAsignadoArticuloUbicacion(ByVal codArt As String, ByVal codUbi As String, ByVal cantidad As Integer, ByVal tipo As String) As Boolean
        Dim valor As Boolean = False
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = ""
            If (tipo = "A") Then
                query = "UPDATE ArticuloUbicacion " & _
                        "SET ASIG_UBI = ASIG_UBI + @cantidad " & _
                        "WHERE ART_Codigo = @articulo " & _
                        "AND UBI_Codigo = @ubicacion " & _
                        "AND UAR_Activo = '1'"
            Else
                query = "UPDATE ArticuloUbicacion " & _
                        "SET ASIG_UBI = ASIG_UBI - @cantidad " & _
                        "WHERE ART_Codigo = @articulo " & _
                        "AND UBI_Codigo = @ubicacion " & _
                        "AND UAR_Activo = '1'"
            End If

            Dim comando As New SqlCommand(query, con)
            With comando
                .CommandText = query
                .Parameters.AddWithValue("@cantidad", cantidad)
                .Parameters.AddWithValue("@articulo", codArt)
                .Parameters.AddWithValue("@ubicacion", codUbi)
                .ExecuteNonQuery()
            End With

            valor = True
        Catch ex As Exception
            valor = False
        Finally
            con.Close()
        End Try

        Return valor
    End Function

    Function obtenerUbicacionDisponible(ByVal codCIA As String, ByVal codSUC As String, ByVal codTipo As String, ByVal codDCA As String, ByVal codArticulo As String, ByVal cantidad As Integer) As String
        Dim texto As String = ""
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            Dim query As String = "SELECT TOP 1 au.UBI_Codigo " & _
                                  "FROM ArticuloUbicacion au " & _
                                  "INNER JOIN Ubicacion u ON u.UBI_Codigo = au.UBI_Codigo " & _
                                  "WHERE au.ART_Codigo = @ART " & _
                                  "AND au.UAR_Stock - ASIG_UBI >= @CAN " & _
                                  "AND au.UBI_Codigo NOT IN( " & _
                                  "SELECT dadpu.UBI_Codigo FROM " & _
                                  "Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " & _
                                  "WHERE dadpu.CIA = @CIA AND dadpu.SUC_Codigo = @SUC AND dadpu.TDOC_Codigo = @TDO " & _
                                  "AND dadpu.DCA_Codigo = @DCA AND dadpu.DADPU_Activo = '1') " & _
                                  "ORDER BY CAST(u.UBI_RACK AS INT)"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@ART", codArticulo)
                .Parameters.AddWithValue("@CAN", cantidad)
                .Parameters.AddWithValue("@CIA", codCIA)
                .Parameters.AddWithValue("@SUC", codSUC)
                .Parameters.AddWithValue("@TDO", codTipo)
                .Parameters.AddWithValue("@DCA", codDCA)
            End With
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(comando)
            Dim ds As New DataSet
            adapter.Fill(ds)

            If ds.Tables(0).Rows.Count <> 0 Then
                texto = ds.Tables(0).Rows(0)("UBI_Codigo").ToString.Trim
            End If
        Catch ex As Exception
            texto = "-1"
        Finally
            con.Close()
        End Try

        Return texto
    End Function

    Function verificarExistenciaUbicacion(ByVal codUbicacion As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()

        Try
            con.Open()

            Dim query As String = "SELECT UBI_Descripcion " & _
                                  "FROM Ubicacion " & _
                                  "WHERE CIA + SUC_Codigo + ALM_Codigo + UBI_Codigo = '" & codUbicacion & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, con)
            adapter.Fill(ds)
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

End Class
